package gdma.hqdes.webapp.controller;

import com.deepoove.poi.data.ChartMultiSeriesRenderData;
import com.deepoove.poi.data.SeriesRenderData;
import common.exception.BadRequestException;
import common.util.TreeUtils;
import gdma.hqdes.article.renderer.UnitScoreTableExcel;
import gdma.hqdes.bean.HqdIndex;
import gdma.hqdes.bean.HqdProject;
import gdma.hqdes.bean.Unit;
import gdma.hqdes.bean.UnitScore;
import gdma.hqdes.service.IndexService;
import gdma.hqdes.service.ProjectService;
import gdma.hqdes.service.UnitService;
import gdma.hqdes.stat.bean.ChartMultiSeriesRenderDataEx;
import gdma.hqdes.stat.bean.IndexCombo;
import gdma.hqdes.stat.bean.StatQuery;
import gdma.hqdes.stat.dao.StatQueryMapper;
import gdma.hqdes.stat.renderer.GeoMapRenderer;
import gdma.hqdes.stat.renderer.UnitScoreDataRenderer;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import io.vavr.Tuple;
import io.vavr.Tuple2;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.authz.annotation.RequiresRoles;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import springfox.documentation.annotations.ApiIgnore;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.stream.Collectors;

import static gdma.hqdes.Constants.ROLE_STATISTICIAN;
import static gdma.hqdes.stat.renderer.GeoMapRenderer.DEFAULT_COLOR_MAPPER;
import static gdma.hqdes.stat.renderer.UnitScoreDataRenderer.convertToMap;

/**
 * @Author wangyue
 * @Version 3.0
 * @Date 2024-07-30
 */

@RestController
@RequestMapping("/api/stat")
@RequiresRoles(ROLE_STATISTICIAN)
public class StatController {

    public static final String DEFAULT_PNG_WIDTH = "800";
    public static final String DEFAULT_PNG_HEIGHT = "600";

    @Autowired
    ProjectService projectService;

    @Autowired
    IndexService indexService;

    @Autowired
    UnitService unitService;

    @Autowired
    StatQueryMapper statQueryMapper;

    @Autowired
    UnitScoreDataRenderer unitScoreDataRenderer;

    @Autowired
    GeoMapRenderer geoMapRenderer;

    Map<Integer, List<Tuple2<Unit, Consumer<Unit>>>> specialParentUnits = new HashMap<Integer, List<Tuple2<Unit, Consumer<Unit>>>>();

    @PostConstruct
    public void initSpecialParentUnits() {
        Unit provinceUnit = unitService.getUnitByShortName("广东省");
        provinceUnit.setAreaTag("_");
        provinceUnit.setId((long)-provinceUnit.getAreaTag().hashCode());
        for(Integer scope : new Integer[] {Unit.SCOPE_MUNICIPAL, Unit.SCOPE_COUNTY}) {
            List<Tuple2<Unit, Consumer<Unit>>> list = new ArrayList<>();
            Tuple2<Unit, Consumer<Unit>> tuple = Tuple.of(provinceUnit, u -> {
                List<Unit> children = unitService.listUnits(null, null, null, null, scope, null, null).getContent();
                u.setChildren(children);
            });
            list.add(tuple);
            for(Map.Entry<String, String> kv : Unit.AREA_TEXTS.entrySet()) {
                Unit unit = new Unit();
                unit.setScope(Unit.SCOPE_PROVINCIAL);
                unit.setAreaTag(kv.getKey() + "_");
                unit.setId((long)-unit.getAreaTag().hashCode());
                unit.setName(kv.getValue());
                unit.setShortName(kv.getValue());
                unit.setShortPrefix(kv.getKey());
                tuple = Tuple.of(unit, u -> {
                    List<Unit> children = unitService.listUnits(null, null, null, null, scope, null, u.getShortPrefix()).getContent();
                    u.setChildren(children);
                });
                list.add(tuple);
            }
            specialParentUnits.put(scope, list);
        }
    }

    public List<Tuple2<Unit, Consumer<Unit>>> getSpecialParentUnits(Integer scope) {
        return specialParentUnits.get(scope);
    }

    public List<Unit> getSpecialChildren(Integer scope, Long unitId) {
        Tuple2<Unit, Consumer<Unit>> specialParent = specialParentUnits.get(scope).stream()
                .filter(t -> unitId.equals(t._1.getId()))
                .findFirst().orElseGet(null);
        if(specialParent != null) {
            specialParent._2.accept(specialParent._1);
            return specialParent._1.getChildren().stream()
                    .collect(Collectors.toList());
        }
        return new ArrayList<>();
    }

    @ApiOperation("指标选择树")
    @GetMapping("/{year}/scopes/{scope}/index-filter")
    public Object indexFilter(@ApiParam("年份") @PathVariable("year") int year,
                              @ApiParam("单位层级：1=省级, 2=地市级, 3=县级") @PathVariable("scope") int scope) {
        HqdProject prj = getProject(year, scope);
        HqdIndex root = indexService.buildThinIndexTree(prj.getIndexVerId());
        Map<String, Object> result = new HashMap<>();
        result.put("hqdIndexes", root);
        result.put("statIndexes", indexService.getStatIndexes(prj.getIndexVerId()));
        return result;
    }

    @ApiOperation("同级单位单指标对比（热力图）")
    @GetMapping("/{year}/scopes/{scope}")
    public ChartMultiSeriesRenderData renderIndexCombos(@ApiParam("年份") @PathVariable("year") int year,
                                                        @ApiParam("单位层级：1=省级, 2=地市级, 3=县级") @PathVariable("scope") int scope,
                                                        @ApiParam("父级单位id") @RequestParam(value = "parentUnitId", required = false) Long parentUnitId,
                                                        @ApiParam("指标项id，二选一") @RequestParam(value = "indexId", required = false) Long indexId,
                                                        @ApiParam("复合指标项id，二选一") @RequestParam(value = "statIndexId", required = false) Long statIndexId) {
        if(indexId == null && statIndexId == null) throw new BadRequestException("必须指定一个：指标项id/复合指标项id");
        StatQuery query = makeHeatmapQuery(year, scope, parentUnitId, indexId, statIndexId);
        ChartMultiSeriesRenderData result = unitScoreDataRenderer.renderHeatmap(query);
        if(scope == Unit.SCOPE_COUNTY)
            //添加所属地市信息
            attachMunicipalInfo(query, (ChartMultiSeriesRenderDataEx)result);
        return result;
    }

    private StatQuery makeHeatmapQuery(int year, int scope, Long parentUnitId, Long indexId, Long statIndexId) {
        StatQuery query = new StatQuery();
        query.setYear(year);
        query.setScope(scope);
        List<Unit> childUnits = null;
        if(parentUnitId == null)
            childUnits =  unitService.getUnits(scope);
        else if(parentUnitId > 0)
            childUnits = unitService.getChildUnits(parentUnitId);
        else {
            childUnits = getSpecialChildren(scope, parentUnitId);
        }
        query.setUnits(childUnits);
        query.setUnitCombos(CollectionUtils.isEmpty(childUnits) ? new ArrayList<>() :
                childUnits.stream().map(u -> u.getId().toString()).collect(Collectors.toList()));
        IndexCombo combo = new IndexCombo();
        if(indexId != null) {
            combo.setType(IndexCombo.TYPE_HQD_INDEX);
            combo.setIndexObject(indexService.getIndex(indexId));
        } else if(statIndexId != null) {
            combo.setType(IndexCombo.TYPE_HQD_INDEX);
            combo.setIndexObject(indexService.getStatIndex(statIndexId));
        }
        combo.setComboType(SeriesRenderData.ComboType.AREA);
        query.setIndexCombo(combo);
        return query;
    }

    private void attachMunicipalInfo(StatQuery query, ChartMultiSeriesRenderDataEx result) {
        Map<Long, Unit> municipalUnits = unitService.getUnits(Unit.SCOPE_MUNICIPAL).stream()
                .collect(Collectors.toMap(Unit::getId, Function.identity()));
        result.addCategoryMetas("parentUnit", shortName -> {
            Unit childUnit = query.getUnits().stream().filter(unit -> shortName.equals(unit.getShortName())).findFirst().get();
            Long parentId = childUnit.getParentId();
            if(parentId != null) {
                Unit parentUnit = municipalUnits.get(parentId);
                if(parentUnit != null) return parentUnit.getShortName();
            }
            return null;
        });
    }

    @ApiOperation("同级单位单指标对比（热力图） 模板引用")
    @GetMapping("/{year}/scopes/{scope}/quote")
    public String quoteIndexCombos(@ApiParam("年份") @PathVariable("year") int year,
                                   @ApiParam("单位层级：1=省级, 2=地市级, 3=县级") @PathVariable("scope") int scope,
                                   @ApiParam("父级单位id") @RequestParam(value = "parentUnitId", required = false) Long parentUnitId,
                                   @ApiParam("指标项id，二选一") @RequestParam(value = "indexId", required = false) Long indexId,
                                   @ApiParam("复合指标项id，二选一") @RequestParam(value = "statIndexId", required = false) Long statIndexId) {
        if(indexId == null && statIndexId == null) throw new BadRequestException("必须指定一个：指标项id/复合指标项id");
        StatQuery query = makeHeatmapQuery(year, scope, parentUnitId, indexId, statIndexId);
        statQueryMapper.insert(query);
        return "{{template.addPicture(" + query.getId() + ")}}";
    }

    @ApiOperation("同级单位单指标对比（热力图） 导出png")
    @GetMapping("/{year}/scopes/{scope}/png")
    public void exportIndexCombos(@ApiParam("年份") @PathVariable("year") int year,
                                  @ApiParam("单位层级：1=省级, 2=地市级, 3=县级") @PathVariable("scope") int scope,
                                  @ApiParam("父级单位id") @RequestParam(value = "parentUnitId", required = false) Long parentUnitId,
                                  @ApiParam("指标项id，二选一") @RequestParam(value = "indexId", required = false) Long indexId,
                                  @ApiParam("复合指标项id，二选一") @RequestParam(value = "statIndexId", required = false) Long statIndexId,
                                  @ApiParam("图片宽，默认800像素") @RequestParam(value = "width", defaultValue = DEFAULT_PNG_WIDTH) int width,
                                  @ApiParam("图片高，默认600像素") @RequestParam(value = "height", defaultValue = DEFAULT_PNG_HEIGHT) int height,
                                  @ApiIgnore HttpServletResponse response) {
        ChartMultiSeriesRenderData renderData = renderIndexCombos(year, scope, parentUnitId, indexId, statIndexId);
        Unit parentUnit = unitService.getUnit(parentUnitId);
        response.setContentType("image/png");
        try(InputStream in = geoMapRenderer.renderImage(parentUnit.getShortName(), DEFAULT_COLOR_MAPPER, convertToMap(renderData), 0, width, height);
            OutputStream out = response.getOutputStream()) {
            IOUtils.copy(in, out);
            out.flush();
        } catch(IOException e) {
            throw new RuntimeException(e);
        }
    }


    @ApiOperation("同级单位指标组合对比")
    @PostMapping("/{year}/scopes/{scope}")
    public ChartMultiSeriesRenderData renderIndexCombos(@ApiParam("年份") @PathVariable("year") int year,
                                                        @ApiParam("单位层级：1=省级, 2=地市级, 3=县级") @PathVariable("scope") int scope,
                                                        @ApiParam("单位id/地区编号列表：逗号分隔 2,3,4,Z,Y") @RequestParam(value = "unitIds", required = false) List<String> unitCombos,
                                                        @ApiParam("父级单位id列表：逗号分隔 2,3,4,5") @RequestParam(value = "parentUnitIds", required = false) List<Long> parentUnitIds,
                                                        @RequestBody List<IndexCombo> indexCombos) {
        List<String> allUnitCombos = makeUnitIdSet(unitCombos, parentUnitIds, scope).stream().collect(Collectors.toList());
        StatQuery query = makeIndexCombosQuery(year, scope, allUnitCombos, indexCombos);
        ChartMultiSeriesRenderData result = unitScoreDataRenderer.renderIndexCombos(query);
        if(scope == Unit.SCOPE_COUNTY)
            //添加所属地市信息
            attachMunicipalInfo((ChartMultiSeriesRenderDataEx)result);
        return result;
    }

    private List<String> makeUnitIdSet(List<String> unitCombos, List<Long> parentUnitIds, Integer scope) {
        Set<String> unitIdSet = new HashSet<>();
        if(!CollectionUtils.isEmpty(unitCombos)) unitIdSet.addAll(unitCombos);
        if(!CollectionUtils.isEmpty(parentUnitIds))
            for(Long parentUnitId : parentUnitIds) {
                List<Long> childUnitIds = parentUnitId > 0 ?
                        unitService.getChildUnits(parentUnitId).stream().map(Unit::getId).collect(Collectors.toList()) :
                        getSpecialChildren(scope, parentUnitId).stream().map(Unit::getId).collect(Collectors.toList());
                unitIdSet.addAll(childUnitIds.stream().map(String::valueOf).collect(Collectors.toList()));
            }
        if(CollectionUtils.isEmpty(unitIdSet)) {
            List<Long> unitIds = unitService.getUnits(scope).stream()
                    .map(Unit::getId) .collect(Collectors.toList());
            unitIdSet.addAll(unitIds.stream().map(String::valueOf).collect(Collectors.toList()));
        }
        return !CollectionUtils.isEmpty(unitIdSet) ? unitIdSet.stream().collect(Collectors.toList()) : null;
    }

    private StatQuery makeIndexCombosQuery(int year, int scope, List<String> unitCombos, List<IndexCombo> indexCombos) {
        StatQuery query = new StatQuery();
        query.setYear(year);
        query.setScope(scope);
        query.setUnitCombos(unitCombos);
        query.setIndexCombos(indexCombos);
        return query;
    }

    private void attachMunicipalInfo(ChartMultiSeriesRenderDataEx result) {
        Map<String, Unit> countryUnits = unitService.getUnits(Unit.SCOPE_COUNTY).stream()
                .collect(Collectors.toMap(Unit::getShortName, Function.identity(), (a, b) -> a));
        Map<Long, Unit> municipalUnits = unitService.getUnits(Unit.SCOPE_MUNICIPAL).stream()
                .collect(Collectors.toMap(Unit::getId, Function.identity()));
        result.addCategoryMetas("parentUnit", shortName -> {
            Unit childUnit = countryUnits.get(shortName);
            if(childUnit != null) {
                Long parentId = childUnit.getParentId();
                if(parentId != null) {
                    Unit parentUnit = municipalUnits.get(parentId);
                    if(parentUnit != null) return parentUnit.getShortName();
                }
            }
            return null;
        });
    }

    @ApiOperation("同级单位指标组合对比 模板引用")
    @PostMapping("/{year}/scopes/{scope}/quote")
    public String quoteIndexCombos(@ApiParam("年份") @PathVariable("year") int year,
                                   @ApiParam("单位层级：1=省级, 2=地市级, 3=县级") @PathVariable("scope") int scope,
                                   @ApiParam("单位id/地区编号列表：逗号分隔 2,3,4,Z,Y") @RequestParam(value = "unitIds", required = false) List<String> unitCombos,
                                   @ApiParam("父级单位id列表：逗号分隔 2,3,4,5") @RequestParam(value = "parentUnitIds", required = false) List<Long> parentUnitIds,
                                   @RequestBody List<IndexCombo> indexCombos) {
        List<String> allUnitCombos = makeUnitIdSet(unitCombos, parentUnitIds, scope).stream().collect(Collectors.toList());
        StatQuery query = makeIndexCombosQuery(year, scope, allUnitCombos, indexCombos);
        statQueryMapper.insert(query);
        return "{{template.addChart(" + query.getId() + ")}}";
    }

    @ApiOperation("同级单位指标组合对比 导出excel")
    @PostMapping("/{year}/scopes/{scope}/excel")
    public void exportIndexCombos(@ApiParam("年份") @PathVariable("year") int year,
                                  @ApiParam("单位层级：1=省级, 2=地市级, 3=县级") @PathVariable("scope") int scope,
                                  @ApiParam("单位id/地区编号列表：逗号分隔 2,3,4,Z,Y") @RequestParam(value = "unitIds", required = false) List<String> unitCombos,
                                  @ApiParam("父级单位id列表：逗号分隔 2,3,4,5") @RequestParam(value = "parentUnitIds", required = false) List<Long> parentUnitIds,
                                  @RequestBody List<IndexCombo> indexCombos,
                                  @ApiIgnore HttpServletResponse response) {
        ChartMultiSeriesRenderData renderData = renderIndexCombos(year, scope, unitCombos, parentUnitIds, indexCombos);
        writeExcelData(response, renderData, "单位指标组合对比.xlsx");
    }

    public static Workbook exportChartToExcel(ChartMultiSeriesRenderData chartData) throws IOException {
        // 创建一个新的工作簿

        Workbook workbook = new XSSFWorkbook();
        // 创建一个新的工作表
        Sheet sheet = workbook.createSheet("Chart Data");

        // 设置标题行的样式为粗体
        CellStyle headerCellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        headerCellStyle.setFont(font);

        // 创建一个标题行
        Row headerRow = sheet.createRow(0);
        Cell headerCell = headerRow.createCell(0);
        headerCell.setCellValue("");
        headerCell.setCellStyle(headerCellStyle);

        // 假设每个系列的数据长度相同，设置其他标题
        int cellNum = 1;
        List<SeriesRenderData> seriesDatas = chartData.getSeriesDatas();
        for(SeriesRenderData seriesData : seriesDatas) {
            headerCell = headerRow.createCell(cellNum++);
            headerCell.setCellValue(seriesData.getName());
            headerCell.setCellStyle(headerCellStyle);
        }

        // 将ChartMultiSeriesRenderData对象的数据写入工作表
        String[] categories = chartData.getCategories();
        for(int rowNum = 1; rowNum <= categories.length; rowNum++) {
            Row row = sheet.createRow(rowNum);
            Cell cell = row.createCell(0);
            cell.setCellValue(categories[rowNum - 1]);
            cell.setCellStyle(headerCellStyle);

            cellNum = 1;
            for (SeriesRenderData seriesData : seriesDatas) {
                cell = row.createCell(cellNum++);
                cell.setCellValue(seriesData.getValues()[rowNum - 1].doubleValue());
            }
        }

        return workbook;
    }

    private static void writeExcelData(HttpServletResponse response, ChartMultiSeriesRenderData renderData, String filname) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filname));
        try(Workbook workbook = exportChartToExcel(renderData);
            OutputStream out = response.getOutputStream()) {
            workbook.write(out);
        } catch(IOException e) {
            throw new RuntimeException(e);
        }
    }

    @ApiOperation("单位历史指标组合对比")
    @PostMapping("/{year0}-{year1}/units/{unitId}")
    public ChartMultiSeriesRenderData renderHistoryIndexCombos(@ApiParam("开始年份") @PathVariable("year0") int year0,
                                                               @ApiParam("结束年份") @PathVariable("year1") int year1,
                                                               @ApiParam("单位id") @PathVariable("unitId") long unitId,
                                                               @RequestBody List<IndexCombo> indexCombos) {
        Unit unit = unitService.getUnit(unitId);
        List<String> unitIds = new ArrayList<>();
        unitIds.add(String.valueOf(unitId));
        StatQuery query = makeHistoryIndexComboQuery(year0, year1, unit.getScope(), unitIds, indexCombos);
        return unitScoreDataRenderer.renderHistoryIndexCombos(query);
    }

    private StatQuery makeHistoryIndexComboQuery(int year0, int year1, int scope, List<String> unitIds, List<IndexCombo> indexCombos) {
        StatQuery query = new StatQuery();
        query.setYear(year0);
        query.setYear1(year1);
        query.setScope(scope);
        query.setUnitCombos(unitIds);
        query.setIndexCombos(indexCombos);
        return query;
    }

    @ApiOperation("单位历史指标组合对比 模板引用")
    @PostMapping("/{year0}-{year1}/units/{unitId}/quote")
    public String quoteHistoryIndexCombos(@ApiParam("开始年份") @PathVariable("year0") int year0,
                                          @ApiParam("结束年份") @PathVariable("year1") int year1,
                                          @ApiParam("单位id") @PathVariable("unitId") long unitId,
                                          @RequestBody List<IndexCombo> indexCombos) {
        Unit unit = unitService.getUnit(unitId);
        List<String> unitIds = new ArrayList<>();
        unitIds.add(String.valueOf(unitId));
        StatQuery query = makeHistoryIndexComboQuery(year0, year1, unit.getScope(), unitIds, indexCombos);
        statQueryMapper.insert(query);
        return "{{template.addChart2(" + query.getId() + ")}}";
    }

    @ApiOperation("单位历史指标组合对比 导出excel")
    @PostMapping("/{year0}-{year1}/units/{unitId}/excel")
    public void exportHistoryIndexCombos(@ApiParam("开始年份") @PathVariable("year0") int year0,
                                         @ApiParam("结束年份") @PathVariable("year1") int year1,
                                         @ApiParam("单位id") @PathVariable("unitId") long unitId,
                                         @RequestBody List<IndexCombo> indexCombos,
                                         @ApiIgnore HttpServletResponse response) {
        ChartMultiSeriesRenderData renderData = renderHistoryIndexCombos(year0, year1, unitId, indexCombos);
        writeExcelData(response, renderData, "单位历史指标组合对比.xlsx");
    }

    @ApiOperation("通用指标数据导入")
    @PostMapping(value = "/{year}/scopes/{scope}/import", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
    public int importProvincialScore(@ApiParam("年份") @PathVariable("year") int year,
                                     @ApiParam("单位层级：1=省级, 2=地市级, 3=县级") @PathVariable("scope") int scope,
                                     @ApiParam("文件") @RequestPart("file") MultipartFile file) {
        if(scope >= Unit.SCOPE_MUNICIPAL && year >= 2024)
            throw new BadRequestException(Unit.SCOPE_TEXTS.get(scope) + "项目只能导入2024年之前的数据");
        HqdProject prj = getProject(year, scope);
        HqdIndex root = indexService.buildThinIndexTree(prj.getIndexVerId());
        int n = 0;
        try (InputStream inputStream = file.getInputStream()) {
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // 跳过第一行
                int cellCount = row.getPhysicalNumberOfCells();
                if (cellCount < 2) continue;
                String shortName = row.getCell(0).getStringCellValue();
                String indexCode = cellCount > 2 ? row.getCell(1).getStringCellValue() : "M";
                float score = (float)row.getCell(cellCount - 1).getNumericCellValue();
                Unit unit = unitService.getUnitByShortName(shortName);
                if(unit == null) continue;
                HqdIndex index = TreeUtils.find(root, i -> i.getCode().equals(indexCode));
                if(index == null) continue;
                UnitScore unitScore = unitService.getScore(prj.getId(), unit.getId(), index.getId());
                if(unitScore == null) {
                    unitScore = new UnitScore();
                    unitScore.setProjectId(prj.getId());
                    unitScore.setUnitId(unit.getId());
                    unitScore.setIndexId(index.getId());
                }
                unitScore.setScore(score);
                unitScore.setUnauditedScore(score);
                unitScore.setStatus(UnitScore.STATUS_APPROVED);
                unitService.saveScore(unitScore);
                n++;
            }
        } catch (IOException x) {
            throw new BadRequestException("导入数据存在错误。");
        }
        return n;
    }

    private HqdProject getProject(int year, int scope) {
        HqdProject prj = projectService.getProject(year, scope);
        if(prj == null) throw new BadRequestException("项目未找到（" + year + "年" + Unit.SCOPE_TEXTS.get(scope) + "），请确保项目已创建");
        return prj;
    }

    @ApiOperation("导出总平均分指标板")
    @GetMapping(value = "/{year}/scopes/{scope}/avg-index-scores/export")
    public void exportAvgIndexScores(@ApiParam("年份") @PathVariable("year") int year,
                                     @ApiParam("单位层级：1=省级, 2=地市级, 3=县级") @PathVariable("scope") int scope,
                                     @ApiParam("层数") @RequestParam(value = "maxLevel", defaultValue = "3") int maxLevel,
                                     @ApiIgnore HttpServletResponse response) {
        HqdProject hqdProject = getProject(year, scope);
        Map<Long, Double> avgIndexScores = unitService.getThinUnitScores(hqdProject.getId()).stream()
                .collect(Collectors.groupingBy(UnitScore::getIndexId, Collectors.averagingDouble(UnitScore::getScore)));
        HqdIndex root = indexService.buildThinIndexTree(hqdProject.getIndexVerId());
        TreeUtils.traverse(root, index -> {
            Double avgIndexScore = avgIndexScores.get(index.getId());
            UnitScore score = new UnitScore();
            score.setScore(avgIndexScore != null ? avgIndexScore.floatValue() : 0f);
            index.setUnitScore(score);
        });
        UnitScoreTableExcel unitScoreTableExcel = new UnitScoreTableExcel(maxLevel);
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            String filename = hqdProject.getName() + "（总体平均分）.xlsx";
            String encodedFilename = URLEncoder.encode(filename, StandardCharsets.UTF_8.toString());
            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFilename);
            OutputStream out = response.getOutputStream();
            unitScoreTableExcel.render(root, out);
            out.flush();
        } catch(Exception x) {
            x.printStackTrace();
        }
    }

    @ApiOperation("导出历史总平均分对比指标板")
    @GetMapping(value = "/{year0}-{year1}/scopes/{scope}/history-avg-index-scores/export")
    public void exportHistoryAvgIndexScores(@ApiParam("开始年份") @PathVariable("year0") int year0,
                                            @ApiParam("结束年份") @PathVariable("year1") int year1,
                                            @ApiParam("单位层级：1=省级, 2=地市级, 3=县级") @PathVariable("scope") int scope,
                                            @ApiParam("层数") @RequestParam(value = "maxLevel", defaultValue = "3") int maxLevel,
                                            @ApiIgnore HttpServletResponse response) {
        AtomicReference<UnitScoreTableExcel.CompositeIndex> rootRef = new AtomicReference<>(null);
        for(int year = year1; year >= year0; year--) {
            HqdProject hqdProject = projectService.getProject(year, scope);
            if(hqdProject == null) continue;
            Map<Long, Double> avgIndexScores = unitService.getThinUnitScores(hqdProject.getId()).stream()
                    .collect(Collectors.groupingBy(UnitScore::getIndexId, Collectors.averagingDouble(UnitScore::getScore)));
            HqdIndex root = indexService.buildThinIndexTree(hqdProject.getIndexVerId());
            TreeUtils.traverse(root, idx -> {
                UnitScoreTableExcel.CompositeIndex index;
                if(rootRef.get() == null) {
                    index = cloneCompositeIndex(idx);
                    rootRef.set(index);
                } else {
                    index = (UnitScoreTableExcel.CompositeIndex)TreeUtils.find(rootRef.get(), i -> i.getCode().equals(idx.getCode()));
                    if(index == null) {
                        index = cloneCompositeIndex(idx);
                        String pIdxCode = idx.getParent().getCode();
                        UnitScoreTableExcel.CompositeIndex parentIndex = (UnitScoreTableExcel.CompositeIndex)TreeUtils.find(rootRef.get(), i -> i.getCode().equals(pIdxCode));
                        parentIndex.addChild(index);
                    }
                }
                Double avgIndexScore = avgIndexScores.get(idx.getId());
                index.getScores().add(avgIndexScore != null ? avgIndexScore.floatValue() : 0f);
            });
        }
        UnitScoreTableExcel unitScoreTableExcel = new UnitScoreTableExcel(maxLevel);
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            String filename = year0 + "_" + year1 + Unit.SCOPE_TEXTS.get(scope) + "（总体平均分）.xlsx";
            String encodedFilename = URLEncoder.encode(filename, StandardCharsets.UTF_8.toString());
            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFilename);
            OutputStream out = response.getOutputStream();
            unitScoreTableExcel.render(rootRef.get(), out, UnitScoreTableExcel.WRITE_COMPOSITE_SCORE, startCell -> {
                Row row = startCell.getRow();
                int startCol = startCell.getColumnIndex();
                int n = 0;
                Cell indexCell = row.getCell(startCol + n, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                indexCell.setCellValue("指标");
                n++;
                for(int year = year1; year >= year0; year--) {
                    Cell scoreCell = row.getCell(startCol + n, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    scoreCell.setCellValue("平均分（" + year + "年）");
                    n++;
                    if(year < year1) {
                        Cell rateCell = row.getCell(startCol + n, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        rateCell.setCellValue("增幅（较" + year + "年）");
                        n++;
                    }
                }
                return n;
            });
            out.flush();
        } catch(Exception x) {
            x.printStackTrace();
        }
    }

    private UnitScoreTableExcel.CompositeIndex cloneCompositeIndex(HqdIndex index) {
        UnitScoreTableExcel.CompositeIndex result = new UnitScoreTableExcel.CompositeIndex();
        result.setName(index.getName());
        result.setCode(index.getCode());
        result.setLevel(index.getLevel());
        return result;
    }

}
